with base_all_11 as (
select
           t.waybill_no,
           t.scan_site ,
           t.scan_site_code ,
           t.scan_time,
           t.shipment_no,
           t.next_station,
           t.next_station_code,
           t.next_network_type,
           t.flg,
           t.rn,
           sum(t.rn) over(partition by t.waybill_no order by scan_time) as rn_1,
           t.sign_time,
           t.network_type
from (
        select 
           t.waybill_no,
           t.scan_site ,
           t.scan_site_code ,
           t.scan_time,
           t.shipment_no,
           t.next_station,
           t.next_station_code,
           t.next_network_type,
           t.flg,
           t.sign_time,
           t.network_type,
           if( scan_site_code=lag(scan_site_code) over(partition by t.waybill_no order by t.scan_time,t.input_time),0,1) as rn
        from (
                 select
                        t.waybill_no,
                        t.scan_site as scan_site,
                        t.scan_site_code as scan_site_code,
                        t.scan_time,
                        null as shipment_no,
                        null as next_station,
                        null as next_station_code,
                        null as next_network_type,
                        '到件' as flg,
                        nvl(t4.sign_later_scantime, t4.outstore_later_scantime) as sign_time,
                        t.network_type,
                        t.input_time,
                        t.upload_time
                 from jms_dwd.dwd_tab_barscan_arrival_base_dt t
                 left join jms_dwd.dwd_wide_sign_summary_waybill_dt t4 on t4.waybill_no = t.waybill_no 
                 where    t.dt >= '{{ execution_date | date_add(-3) | cst_ds }}'  and t.dt <= '{{ execution_date | date_add(-0) | cst_ds }}'
                 and     nvl(t4.sign_later_scantime, t4.outstore_later_scantime) > nvl(t.scan_time, '1111-01-01')     
                 --and t4.dt>='2022-07-11'
                 and t4.dt >= '{{ execution_date | date_add(-3) | cst_ds }}'  and t4.dt <= '{{ execution_date | date_add(-0) | cst_ds }}'
                union all
                 select
                        t.waybill_no,
                        t.input_site as scan_site,
                        t.scan_site_code as scan_site_code,
                        t.scan_time,
                        t.shipment_no,
                        t.next_station,
                        t.next_station_code,
                        t.next_network_type,
                        '发件' as flg,
                        nvl(t4.sign_later_scantime, t4.outstore_later_scantime) as sign_time,
                        t.network_type,
                        t.input_time,
                        t.upload_time
                    from jms_dwd.dwd_tab_barscan_send_base_dt t
                    left join jms_dwd.dwd_wide_sign_summary_waybill_dt t4 on t4.waybill_no = t.waybill_no
                    where    t.dt >= '{{ execution_date | date_add(-3) | cst_ds }}'  and t.dt <= '{{ execution_date | date_add(-0) | cst_ds }}'
                    and  nvl(t4.sign_later_scantime, t4.outstore_later_scantime) > nvl(t.scan_time, '1111-01-01')
                    --and t4.dt>='2022-07-11'
                    and t4.dt >= '{{ execution_date | date_add(-3) | cst_ds }}'  and t4.dt <= '{{ execution_date | date_add(-0) | cst_ds }}'

            ) t
    ) t

),base_all_22 as (
     select        t.waybill_no,
                   t.arrival_site as arrival_site ,
                   t.arrival_site_code as arrival_site_code,
                   min(t.arrival_time) as arrival_time,
                   max(t.shipment_no) as shipment_no,
                   max(t.next_station) as next_station,
                   max(t.next_station_code ) as next_station_code,
                   max(t.next_network_type) as next_network_type,
                   max(t.flg) as flg,
                   t.rn_1,
                   max(t.rn1) as rn1,
                   max(t.rn2) as rn2,
                   max(t.sign_time) as sign_time,
                   max(t.network_type) as network_type,
                   max(t.send_time) send_time,
                   max(t.send_next_type) send_next_type , 
                   max(t.send_next_name) send_next_name , 
                   max(if(t.send_next_type=2,t.send_shipment_no,null)) send_shipment_no ,
                   max(t.next_flg    )     next_flg
   from (  
     
            select  
                   t.waybill_no,
                   t.scan_site as arrival_site ,
                   t.scan_site_code as arrival_site_code,
                   min(t.scan_time) as arrival_time,
                   null as shipment_no,
                   null as next_station,
                   null as next_station_code,
                   null as next_network_type,
                   null as flg,
                   t.rn_1,
                   null as rn1,
                   null as rn2,
                   max(t.sign_time) as sign_time,
                   max(t.network_type) as network_type,
                   null as send_time,
                   null as send_next_type  , 
                   null as send_next_name , 
                   null as send_shipment_no  ,
                   null as next_flg
            from base_all_11 t where t.flg='到件'
            group by  t.waybill_no,
                   t.scan_site ,
                   t.scan_site_code ,
                   t.rn_1
            union all 
           select  
                   t.waybill_no,
                   t.scan_site as arrival_site ,
                   t.scan_site_code as arrival_site_code,
                   null as arrival_time,
                   null as shipment_no,
                   null as next_station,
                   null as next_station_code,
                   null as next_network_type,
                   null as flg,
                   t.rn_1,
                    null as rn1,
                   null as rn2,    
                   t.sign_time as sign_time,
                   t.network_type as network_type,
                   t.scan_time as send_time,
                   next_network_type as send_next_type  ,
                   next_station as send_next_name ,
                   shipment_no as send_shipment_no  ,
                   null as next_flg
            from (
                    select
                     t.waybill_no,
                     t.scan_site ,
                     t.scan_site_code ,
                     t.sign_time ,
                     t.network_type ,
                     t.scan_time  ,
                     t.next_network_type    ,
                     t.next_station   ,
                     t.shipment_no    ,
                     t.rn_1,
                     row_number()over(partition by t.waybill_no,
                                       t.scan_site ,
                                       t.scan_site_code ,
                                       t.rn_1 order by t.scan_time desc ) as rn3
                    from base_all_11 t where t.flg='发件'

                  ) t  where t.rn3=1



    
    ) t  group by t.waybill_no,
                   t.arrival_site ,
                   t.arrival_site_code ,
                   t.rn_1
)

insert overwrite table jms_dm.dm_center_stop_time_dt partition (dt)
select
t.*
,t3.cancal_time
,t2.actual_departure_time as last_time
,to_date(t.arrival_time) as dt
from base_all_22 t 
 left join (
                select
                    t3.waybill_no,
                    t3.cancal_network_code,
                    max(cancal_time) as cancal_time
                from  jms_dwd.dwd_yl_oms_interceptorpiece_base t3
                where   t3.status in (1, 2) 
                group by     
                t3.waybill_no,     
                t3.cancal_network_code
       ) t3 on t.waybill_no = t3.waybill_no and t.arrival_site_code = t3.cancal_network_code
left join (
        select
            shipment_no,
            network_code,
            network_name,
            max(actual_departure_time) as actual_departure_time
        from
            (
                select
                    shipment_no,
                    network_code,
                    network_name,
                    actual_departure_time
                from
                    jms_ods.yl_tmsnew_tms_shipment_stop
                where dt = '{{ execution_date | date_add(-0) | cst_ds }}' and shipment_no not like 'B%'
                union all
                select
                    shipment_no,
                    network_code,
                    network_name,
                    actual_departure_time
                from  jms_ods.yl_tmsnew_branch_shipment_stop
                where dt = '{{ execution_date | date_add(-0) | cst_ds }}' and shipment_no not like 'B%'
         ) t
        group by
            shipment_no,
            network_code,
            network_name

 ) t2 on
        t.arrival_site_code = t2.network_code
        and t.send_shipment_no = t2.shipment_no 
where  date_format(t.arrival_time, 'yyyy-MM-dd') = '{{ execution_date | date_add(-2) | cst_ds }}'  and t.network_type=2
;
